package com.yss.common.core.utils.easyexecl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.enums.WriteTypeEnum;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.yss.common.core.domain.ExcelExp;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.StringUtils;
import com.yss.common.core.utils.uuid.IdUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;

import static org.apache.poi.ss.usermodel.BorderStyle.DASHED;

/**
 * 功能描述: 导出Excel 使用阿里开源框架EasyExcel
 * 开发文档: https://www.yuque.com/easyexcel/doc/write
 *
 * @param:
 */
@Slf4j
public class EasyExeclUtil {
    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(Object excelData, List<?> data, String sheetName) {
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out, excelData.getClass())
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(Object excelData, List<?> data, ExcelTypeEnum typeEnum, String sheetName) {
        initCellMaxTextLength();
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out, excelData.getClass())
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(typeEnum)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(Object excelData, List<?> data, ExcelTypeEnum typeEnum, String[] headers, String sheetName) {
        initCellMaxTextLength();
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out, excelData.getClass())
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(typeEnum)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head(headers)).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 初始化 cell 内容长度
     * cell 原本内容长度限制 32767  现修改为Integer.MAX_VALUE
     */
    public static void initCellMaxTextLength() {
        SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
        if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) {
            Field field;
            try {
                field = excel2007.getClass().getDeclaredField("_maxTextLength");
                field.setAccessible(true);
                field.set(excel2007, Integer.MAX_VALUE);
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [clazz 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(Class<?> clazz, List<?> data) {
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out, clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类, data 导出列表数据, headers 动态表头]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(Object excelData, List<?> data, String[] headers, String sheetName) {
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out, excelData.getClass())
                    .head(head(headers))
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传 (不创建对象的写)
     *
     * @param: [headers, data]
     * @return: byte[]
     */
    public static byte[] exportExcelByte(String[] headers, List<?> data, String sheetName) {
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            excelWriter = EasyExcel.write(out)
                    .head(head(headers))
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel数据,方便于Controllerd的直接页面导出
     *
     * @param: [data 需要导出的数据, head 表头信息]
     * @return: void
     */
    public static void exportExcel(OutputStream outputStream, List<?> data, String[] headers, String sheetName) {
        initCellMaxTextLength();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        try {
            EasyExcel.write(outputStream)
                    .needHead(true)
                    .head(head(headers))
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .sheet(0, sheetName).doWrite(data);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 功能描述: 导出Excel数据,方便于Controllerd的直接页面导出
     *
     * @param: [data 需要导出的数据, head 表头信息]
     * @return: void
     */
    public static void export(HttpServletResponse response, List<?> data, Class<?> clazz) {
        initCellMaxTextLength();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        try {
            // 清空输出流
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''");
            // 定义输出类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            EasyExcel.write(response.getOutputStream(), clazz)
                    .needHead(true)
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(data);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 功能描述: 导出Excel数据,方便于Controllerd的直接页面导出
     *
     * @param: [data 需要导出的数据, head 表头信息]
     * @return: void
     */
    public static void export(HttpServletResponse response, List<?> data, Class<?> clazz, String fileName) {
        initCellMaxTextLength();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        try {
            if (StringUtils.isEmpty(fileName)) {
                // 默认excel文件名
                fileName = IdUtils.fastSimpleUUID() + "." + ExcelTypeEnum.XLSX;
            }
            // 清空输出流
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
            // 定义输出类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            EasyExcel.write(response.getOutputStream(), clazz)
                    .needHead(true)
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(data);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }


    /**
     * 功能描述: 导出Excel数据,方便于Controllerd的直接页面导出
     * 多sheet
     *
     * @param:
     * @return: void
     */
    public static void exportExcelManySheet(HttpServletResponse response, List<ExcelExp> mysheet) {
        initCellMaxTextLength();
        ExcelWriter excelWriter = null;
        try {
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(EasyExeclUtil.headWriteCellStyle(), EasyExeclUtil.contentWriteCellStyle());
            excelWriter = EasyExcel.write(response.getOutputStream())
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new Custemhandler())
                    .build();
            int num = 0;
            for (ExcelExp excelExp : mysheet) {
                WriteSheet sheet = EasyExcel.writerSheet(num, excelExp.getFileName()).build();
                WriteTable table = EasyExcel.writerTable().head(excelExp.getClazz()).needHead(true).build();
                EasyExeclUtil.write(excelExp.getDataset(), excelWriter, sheet, table);
                num++;
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
        } finally {
            if (null != excelWriter) {
                excelWriter.finish();
            }
        }
    }


    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static byte[] exportExcelByteFoDropDownBox(Object excelData, List<?> data, Map<Integer, List<String>> dropDownBoxMaps, Integer startLine, Integer terminateLine, String sheetName) {
        ExcelWriter excelWriter = null;
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try { //                realtiverownum
            excelWriter = EasyExcel.write(out, excelData.getClass())
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new CustomSheetWriteHandler(dropDownBoxMaps, startLine, terminateLine)).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return out.toByteArray();
    }

    /**
     * 功能描述: 导出Excel的数据类型为byte[],方便于OSS的文件上传
     *
     * @param: [excelData 导出列表实体类 , data 导出列表数据]
     * @return: byte[]
     */
    public static void exportExcelByteFoDropDownBox(HttpServletResponse response, Object excelData, List<?> data, Map<Integer, List<String>> dropDownBoxMaps, Integer startLine, Integer terminateLine, String fileName) {

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            if (StringUtils.isEmpty(fileName)) {
                // 默认excel文件名
                fileName = IdUtils.fastSimpleUUID() + "." + ExcelTypeEnum.XLSX;
            }
            // 清空输出流
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
            // 定义输出类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            EasyExcel.write(response.getOutputStream(), excelData.getClass())
                    .needHead(true)
                    .inMemory(true)
                    .registerWriteHandler(new WaterMarkHandler())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new CustomSheetWriteHandler(dropDownBoxMaps, startLine, terminateLine)).sheet().doWrite(data);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 功能描述: 动态头的实时生成
     *
     * @param: [headers 表头数据]
     */
    private static List<List<String>> head(String[] headers) {
        List<List<String>> headList = new ArrayList<>();
        for (int i = 0; i < headers.length; i++) {
            List<String> list = new ArrayList<>();
            list.add(headers[i]);
            headList.add(list);
        }
        return headList;
    }

    /**
     * 设置sheet页读取顺序
     *
     * @param excelReader
     * @param sheetNames
     */
    public static void setReadOrder(ExcelReader excelReader, String... sheetNames) {
        List<String> nameList = Arrays.asList(sheetNames);
        excelReader.excelExecutor().sheetList().sort((a, b) -> {
            int idx1 = nameList.indexOf(a.getSheetName());
            int idx2 = nameList.indexOf(b.getSheetName());
            return idx1 - idx2;
        });
    }

    /**
     * 功能描述: 返回导入的Excel内容
     *
     * @param: [inputStream, fileName, excelListener, excelData]
     * @return: java.util.List<java.lang.Object>
     */
    public static List<Object> getReader(InputStream inputStream, String fileName, ExcelListener listener, Object excelData) {
        //保存返回数据的List
        List<Object> objectList = new ArrayList<>();
        ExcelReader excelReader = null;
        try {
            //判断上传类型是否正确
            if (fileName != null && (fileName.toLowerCase().endsWith(".xls") || fileName.toLowerCase().endsWith(".xlsx"))) {
                excelReader = EasyExcel.read(inputStream, excelData.getClass(), listener).build();
                ReadSheet readSheet = EasyExcel.readSheet(0).build();
                excelReader.read(readSheet);
                objectList = listener.getDatas();
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        return objectList;
    }

    public static List<Object> getReaderClass(InputStream inputStream, ExcelListener listener, Class<?> clazz, int headRowNumber) {
        //保存返回数据的List
        List<Object> objectList = new ArrayList<>();
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(inputStream).build();
            EasyExeclUtil.setReadOrder(excelReader);
            ReadSheet rConstantExcelSheet = EasyExcel.readSheet(0)
                    .head(clazz).headRowNumber(headRowNumber).registerReadListener(listener).build();
            excelReader.read(rConstantExcelSheet);
            objectList = listener.getDatas();
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        return objectList;
    }

    /***
     * 写入数据
     */
    public static void write(List<?> list, ExcelWriter excelWriter, WriteSheet writeSheet, WriteTable writeTable) {
        write(list, excelWriter, writeSheet, writeTable, false, false);
    }

    /***
     * 写入数据，可指定是否追加空行
     * @param appendEmptyRow 数据输出完之后，最近一行空行
     * @param leastOne 当无数据时，添加一行空数据（使下拉框出现）
     */
    @SuppressWarnings("unchecked")
    public static <T> void write(List<T> list, ExcelWriter excelWriter, WriteSheet writeSheet, WriteTable writeTable,
                                 boolean appendEmptyRow, boolean leastOne) {
        boolean nullrow = false;
        if (CollectionUtils.isEmpty(list) && leastOne) {
            list = new ArrayList<T>();
            Class<?> type = writeTable.getClazz();
            try {
                list.add((T) type.newInstance());
            } catch (Exception e) {
                throw new RuntimeException("new instance error, not found no ArgsConstructor with " + type);
            }
            nullrow = true;
        }
        excelWriter.write(list, writeSheet, writeTable);
        if (nullrow) {
            Sheet sheet = excelWriter.writeContext().writeSheetHolder().getSheet();
            Row row = sheet.getRow(sheet.getLastRowNum());
            row.forEach(cell -> cell.setCellValue(""));
        } else if (null != list) {
            list.clear(); // release memory
        }
        if (appendEmptyRow) {
            writeEmptyRow(excelWriter, writeSheet);
        }
    }

    /***
     * 写入一行空行
     */
    public static void writeEmptyRow(ExcelWriter excelWriter, WriteSheet writeSheet) {
        excelWriter.writeContext().currentSheet(writeSheet, WriteTypeEnum.ADD);
        WriteSheetHolder writeSheetHolder = excelWriter.writeContext().writeSheetHolder();
        if (null != writeSheetHolder) {
            writeSheetHolder.getSheet().createRow(writeSheetHolder.getNewRowIndexAndStartDoWrite());
        }
    }

    /**
     * 功能描述: 自定义样式 -- 头部策略
     *
     * @param:
     * @return:
     */
    public static WriteCellStyle headWriteCellStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 设置字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        return headWriteCellStyle;
    }

    /**
     * 功能描述: 自定义样式 -- 内容策略
     *
     * @param:
     * @return: com.alibaba.excel.write.metadata.style.WriteCellStyle
     */
    public static WriteCellStyle contentWriteCellStyle() {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 设置背景颜色
        // contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 设置字体样式
        WriteFont contenWriteFont = new WriteFont();
        // 设置字体大小
        contenWriteFont.setFontHeightInPoints((short) 10);
        // 设置自动换行
        contentWriteCellStyle.setWrapped(false);
        // 设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 设置边框样式
        contentWriteCellStyle.setBorderLeft(DASHED);
        contentWriteCellStyle.setBorderTop(DASHED);
        contentWriteCellStyle.setBorderRight(DASHED);
        contentWriteCellStyle.setBorderBottom(DASHED);

        contentWriteCellStyle.setWriteFont(contenWriteFont);
        return contentWriteCellStyle;
    }

    /**
     * 导出文件
     *
     * @param dataList 文件内容， map的key代表表格的列说明，value代表值
     * @param
     */
    public static void excelExportNew(List<Map<String, Object>> dataList, HttpServletResponse response) {
        excelExportNew(dataList, response, "sheet1");
    }

    /**
     * 导出文件
     *
     * @param dataList 文件内容， map的key代表表格的列说明，value代表值
     * @param
     */
    public static void excelExportNew(List<Map<String, Object>> dataList, HttpServletResponse response, String sheetName) {
        ExcelWriter excelWriter = null;
        if (StringUtils.isEmpty(dataList)) {
            throw new BusinessException("你导出的数据是空数据，请检查！");
        }
        try {
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

            Set<String> keySet = dataList.get(0).keySet();
            EasyExcel.write(response.getOutputStream())
                    .head(head(keySet.toArray(new String[0])))
                    .excelType(ExcelTypeEnum.XLSX)
                    .inMemory(true)
                    .sheet(sheetName)
                    .registerWriteHandler(new WaterMarkHandler())
                    .registerWriteHandler(new Custemhandler())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .doWrite(dataList(dataList, keySet.toArray(new String[0])));
        } catch (Exception e) {
            throw new BusinessException("导出文件出错！");
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

    //设置导出的数据内容
    private static List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {
        List<List<Object>> list = new ArrayList<>();
        dataList.stream().forEach(map -> {
            List<Object> data = new ArrayList<Object>();
            for (int i = 0; i < dataStrMap.length; i++) {
                data.add((map.get(dataStrMap[i])));
            }
            list.add(data);
        });
        return list;
    }
    /**
     * ################ EasyExcel 实体类注解解释 ######################
     * ①: 设置表头信息 @ExcelProperty( value = String[], index =int) 标记在属性上 value: 表名称 index: 列号
     *  特殊风格: @ExcelProperty({"主标题", "字符串标题01"})
     *  特殊风格: @ExcelProperty({"主标题","字符串标题02"})
     *  特殊风格: @ExcelProperty({"主标题", "字符串标题03"})
     *  ▔▔▔▔▔▔▔▔主标题 ▔▔▔▔▔▔▔▔
     *  \字符串标题01\字符串标题02\字符串标题03\
     *
     *
     * ②: 设置列表内容的行高 @ContentRowHeight( index = index; 行高 ) 标记在类上
     *
     * ③: 设置表头的行高 @HeadRowHeight( index = 行高 ) 标记在类上
     *
     * ④: 设置Excel的统一列宽 不写会进行自动列宽,但不准确. @ColumnWidth( index = 行高 ) 标记在类/属性上
     *
     *
     * ⑤: 当属性类型为Date时,可自定义设置年月日格式 @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") 标记在属性上
     *
     * ⑥: 可定义设置数字展示 @NumberFormat("#.##%") 标记在属性上
     *
     * ⑦: 使用注解的方式自定义样式( ps: Util内部已设置自定义样式,除非特殊要求,否则不用设置 )
     *
     * @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10) 头背景设置成红色
     *                            IndexedColors.RED.getIndex()
     * @HeadFontStyle(fontHeightInPoints = 20) 头字体设置成20
     * @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17) 内容的背景设置成绿色
     *                               IndexedColors.GREEN.getIndex()
     * @ContentFontStyle(fontHeightInPoints = 20) 内容字体设置成20
     *
     *                                      ########################################################################
     **/

}
